--region [dbo].[AddGroupUser]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddGroupUser]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddGroupUser]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddGroupUser]
GO

CREATE PROCEDURE [dbo].[AddGroupUser]
	@GroupId int,
	@UserId int,
	@UserName nvarchar(50),
	@Role int,
	@TopicCount int,
	@ReplyCount int,
	@PostCount int,
	@JoinTime datetime
AS

INSERT INTO [GroupUser] (
	[GroupId],
	[UserId],
	[UserName],
	[Role],
	[TopicCount],
	[ReplyCount],
	[PostCount],
	[JoinTime]
) VALUES (
	@GroupId,
	@UserId,
	@UserName,
	@Role,
	@TopicCount,
	@ReplyCount,
	@PostCount,
	@JoinTime
)

GO
--endregion

--region [dbo].[UpdateGroupUserRole]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateGroupUserRole]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateGroupUserRole]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateGroupUserRole]
GO

CREATE PROCEDURE [dbo].[UpdateGroupUserRole]
	@WhereCondition nvarchar(512),
	@Role int
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
UPDATE [GroupUser] SET
	[Role] = ' + STR(@Role) + '
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion

--region [dbo].[UpdateGroupUserTopicNum]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateGroupUserTopicNum]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateGroupUserTopicNum]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateGroupUserTopicNum]
GO

CREATE PROCEDURE [dbo].[UpdateGroupUserTopicNum]
	@GroupId int,
	@UserId int,
	@Num int
AS

UPDATE [GroupUser] SET
	[TopicCount] = [TopicCount] + @Num,
	[PostCount] = [PostCount] + @Num
WHERE
	[GroupId] = @GroupId AND [UserId] = @UserId 

GO
--endregion

--region [dbo].[UpdateGroupUserReplyNum]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateGroupUserReplyNum]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateGroupUserReplyNum]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateGroupUserReplyNum]
GO

CREATE PROCEDURE [dbo].[UpdateGroupUserReplyNum]
	@GroupId int,
	@UserId int,
	@Num int
AS

UPDATE [GroupUser] SET
	[ReplyCount] = [ReplyCount] + @Num,
	[PostCount] = [PostCount] + @Num
WHERE
	[GroupId] = @GroupId AND [UserId] = @UserId 

GO
--endregion 

--region [dbo].[DelGroupUser]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelGroupUser]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelGroupUser]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelGroupUser]
GO

CREATE PROCEDURE [dbo].[DelGroupUser]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[GroupUser]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetGroupUser]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetGroupUser]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetGroupUser]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetGroupUser]
GO

CREATE PROCEDURE [dbo].[GetGroupUser]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	[Id],
	[GroupId],
	[UserId],
	[UserName],
	[Role],
	[TopicCount],
	[ReplyCount],
	[PostCount],
	[JoinTime]
FROM
	[GroupUser]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetGroupUserList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetGroupUserList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetGroupUserList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetGroupUserList]
GO

CREATE PROCEDURE [dbo].[GetGroupUserList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [GroupUser]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	[Id],
	[GroupId],
	[UserId],
	[UserName],
	[Role],
	[TopicCount],
	[ReplyCount],
	[PostCount],
	[JoinTime]
FROM
	[GroupUser]
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	WHERE [Id] < 
	( 
	SELECT MIN([Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Id] 
	FROM [GroupUser] ' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion

--region [dbo].[GetTopGroupUsers]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTopGroupUsers]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTopGroupUsers]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTopGroupUsers]
GO

CREATE PROCEDURE [dbo].[GetTopGroupUsers]
	@N int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT TOP ' + STR(@N) + '
	[Id],
	[GroupId],
	[UserId],
	[UserName],
	[Role],
	[TopicCount],
	[ReplyCount],
	[PostCount],
	[JoinTime]
FROM
	[GroupUser]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END
	
IF @OrderByExpression <> ''
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression 
END	

EXEC sp_executesql @SQL

GO
--endregion

--region [dbo].[GetGroupJoinApplyList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetGroupJoinApplyList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetGroupJoinApplyList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetGroupJoinApplyList]
GO

CREATE PROCEDURE [dbo].[GetGroupJoinApplyList]
    @UserId int,
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([GroupUser].[Id]) 
FROM [GroupUser],[Group]
WHERE
     [GroupUser].[GroupId] = [Group].[Id] AND [Role] = '+STR(-1)+' AND [Group].[AppUserId] = '+STR(@UserId)
	
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
    (SELECT 
	[GroupUser].[GroupId],
	[Group].[GroupName],
	[GroupUser].[UserId],
	[GroupUser].[UserName],
	[GroupUser].[JoinTime],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM
	[GroupUser],[Group]
WHERE
     [GroupUser].[GroupId] = [Group].[Id] AND [Role] = '+STR(-1)+' AND [Group].[AppUserId] = '+STR(@UserId)
     
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO

--endregion
